package dao;

import entity.CommunityInfo;
import openConn.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CommunityDao {

    public static int update(int comId,Double buyCount) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "update community_info set com_count = ? where com_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setDouble(1,buyCount);
        statement.setInt(2,comId);
        int count = statement.executeUpdate();
        statement.close();
        connection.close();
        return count;

    }
    public static int update(int comId,Double buyCount,Connection connection) throws SQLException {
        String sql = "update community_info set com_count = ? where com_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setDouble(1,buyCount);
        statement.setInt(2,comId);
        int count = statement.executeUpdate();
        statement.close();
        return count;

    }


    public static List<CommunityInfo> selectCommunity() throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "CALL select_community()";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        List<CommunityInfo> communityInfoList = new ArrayList<>();
        CommunityInfo communityInfo = null;
        while (resultSet.next()){
            int comId = resultSet.getInt("com_id");
            String className = resultSet.getString("classify_name");
            String comName = resultSet.getString("com_name");
            Double comPrice = resultSet.getDouble("com_price");
            Double comCount = resultSet.getDouble("com_count");
            communityInfo = new CommunityInfo(comId,className,comName,comPrice,comCount);
            communityInfoList.add(communityInfo);
        }
        resultSet.close();
        statement.close();
        connection.close();
        return communityInfoList;
    }
    public static CommunityInfo selectById(int comId) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "select * from community_info where com_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,comId);
        ResultSet resultSet = statement.executeQuery();
        CommunityInfo communityInfo = null;
        while (resultSet.next()){
            String className = resultSet.getString("classify_id");
            String comName = resultSet.getString("com_name");
            Double comPrice = resultSet.getDouble("com_price");
            Double comCount = resultSet.getDouble("com_count");
            communityInfo = new CommunityInfo(comId,className,comName,comPrice,comCount);
        }
        resultSet.close();
        statement.close();
        connection.close();
        return communityInfo;
    }
    public static CommunityInfo selectById(int comId ,Connection connection) throws SQLException {
        String sql = "select * from community_info where com_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,comId);
        ResultSet resultSet = statement.executeQuery();
        CommunityInfo communityInfo = null;
        while (resultSet.next()){
            String className = resultSet.getString("classify_id");
            String comName = resultSet.getString("com_name");
            Double comPrice = resultSet.getDouble("com_price");
            Double comCount = resultSet.getDouble("com_count");
            communityInfo = new CommunityInfo(comId,className,comName,comPrice,comCount);
        }
        resultSet.close();
        statement.close();

        return communityInfo;
    }

    public static void insert(CommunityInfo communityInfo) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "insert into community_info(classify_id,com_name,com_price,com_count) values (?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,communityInfo.getClassifyId());
        statement.setString(2,communityInfo.getComName());
        statement.setDouble(3,communityInfo.getComPrice());
        statement.setDouble(4,communityInfo.getComCount());
        int count = statement.executeUpdate();
        statement.close();
        connection.close();
    }

    public static void update(CommunityInfo communityInfo1) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "update community_info set classify_id = ?,com_name = ?,com_price = ?,com_count = ? where com_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,communityInfo1.getClassifyId());
        statement.setString(2,communityInfo1.getComName());
        statement.setDouble(3,communityInfo1.getComPrice());
        statement.setDouble(4,communityInfo1.getComCount());
        statement.setInt(5,communityInfo1.getComId());
        int count = statement.executeUpdate();
        statement.close();

    }

    public static void delete(int comId1) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "delete from community_info where com_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,comId1);
        int count = statement.executeUpdate();
        statement.close();
        connection.close();
    }
}
